

********** RECURRENT EXPENDITURE **********
#delimit;

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Tigray"); 		  ren Ti var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Ti_E; tempfile rec_Tigray; 		save `rec_Tigray';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Afar");   		  ren Af var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Af_E; tempfile rec_Afar; 			save `rec_Afar';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Amhara"); 		  ren Am var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Am_E; tempfile rec_Amhara; 		save `rec_Amhara';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Oromia"); 		  ren Or var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Or_E; tempfile rec_Oromia; 		save `rec_Oromia';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Somale"); 		  ren So var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E So_E; tempfile rec_Somale; 		save `rec_Somale';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Benishangul");   ren Be var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Be_E; tempfile rec_Benishangul; 	save `rec_Benishangul';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("SNNP"); 		  ren SN var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E SN_E; tempfile rec_SNNP; 			save `rec_SNNP';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Gambella"); 	  ren Ga var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Ga_E; tempfile rec_Gambella; 		save `rec_Gambella';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("Harari"); 		  ren Ha var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Ha_E; tempfile rec_Harari; 		save `rec_Harari';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("AddisAbaba"); 	  ren Ad var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Ad_E; tempfile rec_AddisAbaba; 	save `rec_AddisAbaba';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("DireDawa"); 	  ren Di var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E Di_E; tempfile rec_DireDawa; 		save `rec_DireDawa';	

import excel using "Table S3 xlsx1.xlsx", clear first sheet("TotalRegional"); ren To var; 
do "Table S3 suppl do1"; g origsort_rec=_n; renpfix E To_E; tempfile rec_TotalRegional; save `rec_TotalRegional';	

use 				`rec_Tigray', clear; 
merge 1:1 var using `rec_Afar'; 		 drop _m;
merge 1:1 var using `rec_Amhara';		 drop _m;
merge 1:1 var using `rec_Oromia';		 drop _m;
merge 1:1 var using `rec_Somale';		 drop _m;
merge 1:1 var using `rec_Benishangul';	 drop _m;
merge 1:1 var using `rec_SNNP';			 drop _m;
merge 1:1 var using `rec_Gambella'; 	 drop _m;
merge 1:1 var using `rec_Harari';		 drop _m;
merge 1:1 var using `rec_AddisAbaba';	 drop _m;
merge 1:1 var using `rec_DireDawa';      drop _m;
merge 1:1 var using `rec_TotalRegional'; drop _m;	
#delimit cr
sort orig
tempfile recurrent_regions
save 	`recurrent_regions'


********** CAPITAL EXPENDITURE **********

#delimit;
import excel using "Table S3 xlsx2.xlsx", clear first sheet("Tigray");		  ren Ti var; 
do "Table S3 suppl do2"; renpfix E Ti_E; g origsort_cap=_n; tempfile cap_Tigray; 		save `cap_Tigray';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Afar");		  ren Af var; 
do "Table S3 suppl do2"; renpfix E Af_E; g origsort_cap=_n; tempfile cap_Afar; 			save `cap_Afar';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Amhara");		  ren Am var; 
do "Table S3 suppl do2"; renpfix E Am_E; g origsort_cap=_n; tempfile cap_Amhara; 		save `cap_Amhara';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Oromia");		  ren Or var; 
do "Table S3 suppl do2"; renpfix E Or_E; g origsort_cap=_n; tempfile cap_Oromia; 		save `cap_Oromia';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Somale");		  ren So var; 
do "Table S3 suppl do2"; renpfix E So_E; g origsort_cap=_n; tempfile cap_Somale; 		save `cap_Somale';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Benishagul");	  ren Be var; 
do "Table S3 suppl do2"; renpfix E Be_E; g origsort_cap=_n; tempfile cap_Benishagul; 	save `cap_Benishagul';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("SNNP");		  ren SN var; 
do "Table S3 suppl do2"; renpfix E SN_E; g origsort_cap=_n; tempfile cap_SNNP; 			save `cap_SNNP';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Gambella");	  ren Ga var; 
do "Table S3 suppl do2"; renpfix E Ga_E; g origsort_cap=_n; tempfile cap_Gambella; 		save `cap_Gambella';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("Harari");		  ren Ha var; 
do "Table S3 suppl do2"; renpfix E Ha_E; g origsort_cap=_n; tempfile cap_Harari; 		save `cap_Harari';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("AddisAbaba");	  ren Ad var; 
do "Table S3 suppl do2"; renpfix E Ad_E; g origsort_cap=_n; tempfile cap_AddisAbaba; 	save `cap_AddisAbaba';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("DireDawa"); 	  ren Di var; 
do "Table S3 suppl do2"; renpfix E Di_E; g origsort_cap=_n; tempfile cap_DireDawa; 		save `cap_DireDawa';

import excel using "Table S3 xlsx2.xlsx", clear first sheet("TotalRegional"); ren To var; 
do "Table S3 suppl do2"; renpfix E To_E; g origsort_cap=_n; tempfile cap_TotalRegional; save `cap_TotalRegional';


use 				`cap_Tigray', clear; 
merge 1:1 var using `cap_Afar'; 	   	 drop _m;
merge 1:1 var using `cap_Amhara'; 	   	 drop _m;
merge 1:1 var using `cap_Oromia'; 	   	 drop _m;
merge 1:1 var using `cap_Somale'; 	   	 drop _m;
merge 1:1 var using `cap_Benishagul'; 	 drop _m;
merge 1:1 var using `cap_SNNP'; 	   	 drop _m;
merge 1:1 var using `cap_Gambella';	   	 drop _m;
merge 1:1 var using `cap_Harari'; 	   	 drop _m;
merge 1:1 var using `cap_AddisAbaba'; 	 drop _m;
merge 1:1 var using `cap_DireDawa'; 	 drop _m;
merge 1:1 var using `cap_TotalRegional'; drop _m;	
#delimit cr
sort orig
tempfile capital_regions
save	`capital_regions'



********** COMBINE RECURRENT & CAPITAL EXPENDITURE **********

use  				  `capital_regions', clear 
merge 1:1 var using `recurrent_regions'
lab var var "var"

for any Ti Af Am Or So Be SN Ga Ha Ad Di To: egen    X_EFY2001_w_all = rowtotal( X_EFY2001_w_rec  X_EFY2001_w_cap)  
egen  DEC_EFY2001_w_all = rowtotal(Ti_EFY2001_w_all Am_EFY2001_w_all Or_EFY2001_w_all SN_EFY2001_w_all) 	 
egen SNON_EFY2001_w_all = rowtotal(Af_EFY2001_w_all Be_EFY2001_w_all Ga_EFY2001_w_all)
renvars, sub(EFY2001 2008)
aorder
 order var 
sort origsort_rec
format *all %17.4fc
g fix=1
drop _m
lab var var "all variables are in million birr"
tempfile before_agpluswater
save	`before_agpluswater'
keep if strpos(var,"agric")>0 | strpos(var,"water")>0
collapse (last) var (sum) *2008_w_all* (mean) fix 
replace var="Ag+Water"
append using `before_agpluswater'
tempfile before_pop
save	`before_pop'


********** POPULATION DATA **********H
use "Table S3 dta.dta", clear
replace region="Am" if region=="Amhara"
replace region="Or" if region=="Oromiya"
replace region="SN" if region=="SNNPR"
replace region="Ti" if region=="Tigray"
replace region="Af" if region=="Affar"
replace region="Be" if region=="BG"
replace region="Ga" if region=="Gambela"
replace region="Ha" if region=="Harari"
replace region="So" if region=="Somali"
replace region="Ad" if region=="Addis_Abeba"
replace region="Di" if region=="Dire_Dawa"
format region %-2s
sort region
g n=_n
g fix=1
drop region
reshape wide pop*, i(fix) j(n)
rensfix 10 _So
rensfix 11 _Ti
rensfix  1 _Ad
rensfix  2 _Af
rensfix  3 _Am
rensfix  4 _Be
rensfix  5 _Di
rensfix  6 _Ga
rensfix  7 _Ha
rensfix  8 _Or
rensfix  9 _SN

egen pop2008_DEC = rowtotal(pop2008_Am pop2008_Or pop2008_SN pop2008_Ti)
egen pop2008_SNON= rowtotal(pop2008_Af pop2008_Ga pop2008_Be)


********** PER CAPITA SPENDING; RATIO OF [D] TO NON-[D] **********

merge 1:m fix using `before_pop'
drop 	  fix
for any DEC SNON Ad Af Am Be Di Ga Ha Or SN So Ti: g X_perc2008_w_all = X_2008_w_all*1000000/pop2008_X
g Sratio_perc2008_w_all= DEC_perc2008_w_all /	  SNON_perc2008_w_all
lab var var ""
g 		order=1 if strpos(var,"ag")>0
replace order=2 if strpos(var,"wa")>0
replace order=3 if strpos(var,"Ag")>0
replace order=4 if order==.
sort order
keep var *perc* DEC_p* SNON_p*
drop Ad* So* Ha* Di*
order var Am* Or* SN_* Ti* DEC* Af* Be* Ga* SNON*
format *all %20.1fc
lab var Am 	"Amhara region per capita local government expenditures (in birr), 2008"
lab var Or 	"Oromia region per capita local government expenditures (in birr), 2008"
lab var SN_ "SNNPR region per capita local government expenditures (in birr), 2008"
lab var Ti 	"Tigray region per capita local government expenditures (in birr), 2008"
lab var Af 	"Afar region per capita local government expenditures (in birr), 2008"
lab var Be 	"Beneshangul-Gumuz region per capita local gov't expenditures (in birr), 2008"
lab var Ga 	"Gambella region per capita local government expenditures (in birr), 2008"
lab var DE 	"Decentralised study regions' per capita local gov't spending (in birr), 2008"
lab var SNO "Non-decentralised study regions' per capita local gov't spending (in birr), 2008"
lab var Sr  "Ratio of decentr. to non-decentr. regions' per capita local gov't spending, 2008"
export excel using "Table S3 results.xlsx", replace first(varl)

*===============================================================================
